#!/usr/bin/env python
# -*- coding: utf-8 -*-

from datetime import datetime
from typing import Optional, List

from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, Enum, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
from sqlalchemy.orm import relationship

# 创建基类
Base = declarative_base()


class Project(Base):
    """项目表"""
    
    __tablename__ = "projects"
    
    id = Column(String(36), primary_key=True, comment="项目ID")
    name = Column(String(255), nullable=False, comment="项目名称")
    description = Column(Text, nullable=True, comment="项目描述")
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), comment="更新时间")
    
    # 关系
    training_data = relationship("TrainingData", back_populates="project", cascade="all, delete-orphan")
    query_history = relationship("QueryHistory", back_populates="project", cascade="all, delete-orphan")
    
    def to_dict(self):
        """将模型转换为字典"""
        return {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None
        }


class TrainingData(Base):
    """训练数据表"""
    
    __tablename__ = "training_data"
    
    id = Column(Integer, primary_key=True, autoincrement=True, comment="训练数据ID")
    project_id = Column(String(36), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, comment="项目ID")
    type = Column(Enum("ddl", "question_sql", "description", name="training_data_type"), nullable=False, comment="数据类型")
    content = Column(Text, nullable=False, comment="内容")
    question = Column(Text, nullable=True, comment="问题")
    sql = Column(Text, nullable=True, comment="SQL查询")
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    
    # 关系
    project = relationship("Project", back_populates="training_data")


class QueryHistory(Base):
    """查询历史表"""
    
    __tablename__ = "query_history"
    
    id = Column(Integer, primary_key=True, autoincrement=True, comment="历史ID")
    project_id = Column(String(36), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, comment="项目ID")
    type = Column(Enum("generation", "correction", name="query_history_type"), nullable=False, comment="历史类型")
    question = Column(Text, nullable=False, comment="问题")
    generated_sql = Column(Text, nullable=False, comment="生成的SQL")
    error_sql = Column(Text, nullable=True, comment="错误的SQL")
    error_message = Column(Text, nullable=True, comment="错误信息")
    db_type = Column(String(50), nullable=False, default="mysql", comment="数据库类型")
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    
    # 关系
    project = relationship("Project", back_populates="query_history")